Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶

Angaben Studierende(r) (fehlende Angaben ergänzen)¶

Vorname: Alexander
Nachname: Wielengowski
Immatrikulationsnummer: 24-672-380
Modul: Data Science
Prüfungsdatum / Raum / Zeit: 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45
Erlaubte Hilfsmittel: w.MA.XX.DS.24HS (Data Science)
Open Book, Eigener Computer, Internet-Zugang
Nicht erlaubt: Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT)
sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen.

Bewertungskriterien¶

(max. erreichbare Punkte: 48)¶

Kategorie Beschreibung Punkteverteilung
Code nicht lauffähig oder Ergebnisse nicht sinnvoll Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. 0 Punkte
Code lauffähig, aber mit gravierenden Mängeln Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. 25% der max. erreichbaren Punkte
Code lauffähig, aber mit mittleren Mängeln Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. 50% der max. erreichbaren Punkte
Code lauffähig, aber mit minimalen Mängeln Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. 75% der max. erreichbaren Punkte
Code lauffähig und korrekt Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. 100% der max. erreichbaren Punkte

Python Libraries und Settings¶

In [1]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text

# Ignore warnings
import warnings
warnings.filterwarnings("ignore")

print(os.getcwd())

/workspaces/python_postgresql_postgis

Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶

1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶

GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶

Hinweis:

  • Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
  • Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.

2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶

In [2]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"

# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
                    "@" + host + ":" + port + "/" + database

# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)

# Test der Connection
with engine.connect() as connection:
    result = connection.execute(text('SELECT current_database()'))
    print(result.fetchone())

# Verbindung trennen
engine.dispose()

('osm_switzerland',)

Aufgaben (Dieser Teil wird bewertet!)¶

Hinweise zu den folgenden Aufgabenstellungen:

  • In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
    1. Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
    2. Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
  • In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
  • In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Beachten Sie, dass für die Punktevergabe auch die weiteren Anforderungen zu den Fragen unter 'Details zur Aufgabenstellung' herangezogen werden.

Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
  • Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
  • Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [7]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """
      SELECT p.osm_id, 	p.name, p."addr:country", st_transform(p.way, 4326) AS geom
      FROM public.planet_osm_point AS p
      WHERE shop='car_repair' 
      --AND p."addr:street" IS NOT NULL 
      --AND p."addr:country"='CH'
      """
### Da nicht explizit genannt, habe ich leere Strasse und Land=CH nur als Kommentar versehen.

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[7]:
osm_id name addr:country geom
0 1811755810 Grenzgarage None POINT (9.62898 47.45412)
1 9408250312 Gebr. Wirth AG None POINT (9.63098 47.45327)
2 2539306181 Lantech None POINT (9.58444 47.46663)
3 2530851973 Garage Welpe None POINT (9.58777 47.46975)
4 5254765356 Richner AG None POINT (9.43594 47.50471)
... ... ... ... ...
1459 4171944125 Garage Biefer CH POINT (9.16975 47.61128)
1460 4171944126 Garage plus None POINT (9.1698 47.60761)
1461 4386729493 Garage Stahel None POINT (9.26639 47.55844)
1462 1724458441 Garage Diethelm None POINT (9.29697 47.53936)
1463 6092408245 Nussberger Direktimport None POINT (9.34644 47.53412)

1464 rows × 4 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [8]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[8]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [5]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """
        SELECT p.osm_id, p.name, st_transform(p.way, 4326) AS geom
        FROM public.planet_osm_point AS p
        WHERE amenity='biergarten'      
        --	AND p."addr:country"='CH'
     ;"""
### country='CH' wurde dieses Mal bewusst ignoriert, da es sonst nur 3 Biergarten hätte. Der Datensatz beinhaltet aber sowieso 
### nur die Schweiz, daher habe ich es ausnahmsweise weggelassen


# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[5]:
osm_id name geom
0 704467869 Bierhalle POINT (9.6068 47.40694)
1 6741052485 None POINT (6.10754 46.16777)
2 7554497969 La Grange POINT (7.15423 46.34937)
3 454394986 None POINT (7.8382 46.54507)
4 5165144561 Osteria Sascola POINT (8.5852 46.30886)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [6]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[6]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
  • Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
  • Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
  • Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
  • Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.

(max. erreichbare Punkte: 6)

In [11]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT p.osm_id,  p."addr:street",  p."addr:housenumber",  p."addr:city",  p."addr:postcode",  p.building,  ST_Transform(p.way, 4326)  AS Geom
        FROM public.planet_osm_polygon AS p
        WHERE p."addr:street"='Stadthausstrasse'
            AND p."addr:city"='Winterthur'
            AND p."addr:housenumber" IS NOT NULL
            AND p.building IS NOT NULL
            AND p."addr:postcode" IS NOT NULL
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[11]:
osm_id addr:street addr:housenumber addr:city addr:postcode building geom
0 9264543 Stadthausstrasse 4a Winterthur 8400 government POLYGON ((8.73111 47.50115, 8.73112 47.50085, ...
1 22301937 Stadthausstrasse 4 Winterthur 8400 apartments POLYGON ((8.73232 47.50109, 8.73233 47.50107, ...
2 75027485 Stadthausstrasse 31 Winterthur 8400 office POLYGON ((8.73162 47.50041, 8.73165 47.50029, ...
3 75027472 Stadthausstrasse 35 Winterthur 8400 apartments POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8...
4 75027503 Stadthausstrasse 37 Winterthur 8400 apartments POLYGON ((8.73146 47.5004, 8.73149 47.50028, 8...
... ... ... ... ... ... ... ...
56 94283231 Stadthausstrasse 145 Winterthur 8400 apartments POLYGON ((8.72414 47.4995, 8.72417 47.49939, 8...
57 94283304 Stadthausstrasse 143 Winterthur 8400 office POLYGON ((8.72427 47.49952, 8.72431 47.4994, 8...
58 24804763 Stadthausstrasse 22 Winterthur 8400 office POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8...
59 26992511 Stadthausstrasse 24 Winterthur 8400 retail POLYGON ((8.72405 47.49972, 8.72407 47.49967, ...
60 134980581 Stadthausstrasse 10b Winterthur 8400 yes POLYGON ((8.72652 47.50075, 8.72661 47.50063, ...

61 rows × 7 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [13]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=17, 
               tiles='ESRIWorldImagery')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[13]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [14]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT 
    osm_id,
	highway,
    ST_Transform(p.way, 4326)  AS Geom
FROM planet_osm_roads AS p
WHERE
	highway='motorway'
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[14]:
osm_id highway geom
0 1236416666 motorway LINESTRING (9.64218 47.43433, 9.6422 47.43402,...
1 552469430 motorway LINESTRING (9.64232 47.43412, 9.6423 47.43469)
2 552469432 motorway LINESTRING (9.6423 47.43469, 9.64232 47.43504,...
3 552469428 motorway LINESTRING (9.64312 47.43793, 9.6429 47.43753,...
4 186132194 motorway LINESTRING (9.64235 47.43533, 9.6424 47.43568,...
... ... ... ...
8263 100501651 motorway LINESTRING (9.15834 47.65468, 9.15789 47.65424...
8264 277760690 motorway LINESTRING (9.16103 47.65931, 9.16112 47.65984...
8265 27158313 motorway LINESTRING (9.16125 47.66169, 9.16124 47.66165...
8266 318438636 motorway LINESTRING (9.16136 47.66118, 9.1614 47.66144)
8267 63904479 motorway LINESTRING (9.1614 47.66144, 9.16145 47.66172,...

8268 rows × 3 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [15]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=9, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    line_weight=3,
    line_color='red'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[15]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features
  • Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.

(max. erreichbare Punkte: 8)

In [29]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
    p.osm_id,
    p.name,
    ST_TRANSFORM(ST_UNION(ST_Buffer(p.way::geometry, 2000)), 4326) AS geom
FROM
    public.planet_osm_line AS p
WHERE
    p.waterway='river'
GROUP BY p.osm_id, p.name
     ;"""
### Buffer scheint nicht zu funktionieren


# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[29]:
osm_id name geom
0 4223448 Äussere Aare POLYGON ((7.61135 46.75204, 7.61112 46.75215, ...
1 4224616 Innere Aare POLYGON ((7.62089 46.74728, 7.62027 46.74748, ...
2 4245442 Reuss POLYGON ((8.61599 46.8591, 8.61509 46.86111, 8...
3 4245448 Reuss POLYGON ((8.62089 46.82734, 8.62063 46.82792, ...
4 4254242 Töss POLYGON ((8.53617 47.55367, 8.53615 47.55396, ...
... ... ... ...
2552 1317015786 Ticino POLYGON ((8.74907 46.50162, 8.75252 46.50114, ...
2553 1317015787 Ticino POLYGON ((8.73866 46.50069, 8.73864 46.50073, ...
2554 1318731230 Le Rhône POLYGON ((6.00715 46.18904, 6.00742 46.1891, 6...
2555 1319653743 None POLYGON ((10.11183 46.68295, 10.11115 46.68303...
2556 1319653744 None POLYGON ((10.10057 46.68987, 10.09988 46.69099...

2557 rows × 3 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [31]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='blue'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[31]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
  • Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
  • Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [22]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT p.osm_id, p.name, p.shop, p."addr:city",  ST_Transform(p.way, 4326)  AS Geom
        FROM public.planet_osm_polygon AS p
        WHERE p.shop='bakery'
		AND (p."addr:city"='Winterthur' OR p."addr:city"='Zürich')
		ORDER BY p.osm_id
     ;"""
     ### sehr wenige Daten. Habe ich als "city" dief falsche Spalte erwischt?

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[22]:
osm_id name shop addr:city geom
0 182237939 Bäckerei Hug bakery Zürich POLYGON ((8.51871 47.36999, 8.51873 47.36998, ...
1 380090582 Brezelkönig bakery Winterthur POLYGON ((8.72323 47.49977, 8.72329 47.49974, ...
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [23]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=12, 
               tiles='ESRIWorldGrayCanvas')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[23]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶

Details zur Aufgabenstellung:

  • Sie finden die Daten in der Tabelle 'planet_osm_point'.
  • Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
  • Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [24]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            p.osm_id,
            p.shop,
            p.name,
            ST_Distance(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
            ) AS distance_meters,
            ST_TRANSFORM(p.way, 4326) AS geom
        FROM
            planet_osm_point AS p
        WHERE
            p.shop = 'hairdresser'
            AND ST_DWithin(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
                500
            )
        ORDER BY distance_meters
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[24]:
osm_id shop name distance_meters geom
0 4833061523 hairdresser André Joe Coiffure 72.419054 POINT (8.54016 47.37774)
1 4424939218 hairdresser McCoiffure 111.635316 POINT (8.53791 47.37791)
2 4244059289 hairdresser Art Coiffure Kaiser 133.439602 POINT (8.53788 47.37745)
3 4424939145 hairdresser Saleh 151.462092 POINT (8.54071 47.37709)
4 693318659 hairdresser Coiffeur Insieme 168.219919 POINT (8.53992 47.37664)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [25]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=16, 
               tiles='ESRIWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[25]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶

In [32]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime

print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')

-----------------------------------
POSIX
Linux | 6.5.0-1025-azure
Datetime: 2024-10-07 10:07:44
Python Version: 3.12.1
IP Address: 127.0.0.1
-----------------------------------